#!coding=utf-8
import requests
import os
import re
import json
import datetime
import time
import pandas as pd
from requests.packages.urllib3.exceptions import InsecureRequestWarning

requests.packages.urllib3.disable_warnings(InsecureRequestWarning)


def raw(text):  # 转化URL字符串

    escape_dict = {
        '/': '%252F',
        '?': '%253F',
        '=': '%253D',
        ':': '%253A',
        '&': '%26',

    }
    new_string = ''
    for char in text:
        try:
            new_string += escape_dict[char]
        except KeyError:
            new_string += char
    return new_string


def mmm(item):
    print("-- start -- ")
    item = raw(item)
    url = 'https://apapia.manmanbuy.com/ChromeWidgetServices/WidgetServices.ashx'
    s = requests.session()
    headers = {
        'Host': 'apapia.manmanbuy.com',
        'Content-Type': 'application/x-www-form-urlencoded; charset=utf-8',
        'Proxy-Connection': 'close',
        'Cookie': 'ASP.NET_SessionId=uwhkmhd023ce0yx22jag2e0o; jjkcpnew111=cp46144734_1171363291_2017/11/25',
        'User-Agent': 'Mozilla/5.0 (iPhone; CPU iPhone OS 10_3_3 like Mac OS X) AppleWebKit/603.3.8 (KHTML, like Gecko) Mobile/14G60 mmbWebBrowse',
        'Content-Length': '457',
        'Accept-Encoding': 'gzip',
        'Connection': 'close',
    }
    postdata = 'c_devid=2C5039AF-99D0-4800-BC36-DEB3654D202C&username=&qs=true&c_engver=1.2.35&c_devtoken=&c_devmodel=iPhone%20SE&c_contype=wifi&' \
               't=1537348981671&c_win=w_320_h_568&p_url={}&' \
               'c_ostype=ios&jsoncallback=%3F&c_ctrl=w_search_trend0_f_content&methodName=getBiJiaInfo_wxsmall&c_devtype=phone&' \
               'jgzspic=no&c_operator=%E4%B8%AD%E5%9B%BD%E7%A7%BB%E5%8A%A8&c_appver=2.9.0&bj=false&c_dp=2&c_osver=10.3.3'.format(
        item)
    s.headers.update(headers)
    req = s.get(url=url, data=postdata, verify=False).text
    # req = s.get(url=url, data=postdata, verify=False).text
    # print(req)
    try:
        js = json.loads(req)
        title = js['single']['title']  ##名称
    except Exception as e:
        print(e)
        # exit(mmm(item))
    ###数据清洗
    # print(js)
    if js['ok'] == 0:
        return
    pic = js['single']['smallpic']  ##图片
    jiagequshi = js['single']['jiagequshi']  ##价格趋势
    jiagequshiyh = js['single']['jiagequshiyh']  ##价格趋势
    lowerPrice = js['single']['lowerPrice']  ##最低价格
    lowerDate = js['single']['lowerDate']  ##最低价格日期
    lowerDate = re.search('[1-9]\d{0,9}', lowerDate).group(0)
    # print(lowerDate)
    lowerDate = time.strftime("%Y-%m-%d", time.localtime(int(lowerDate)))
    itemurl = js['single']['url']  ##商品链接
    qushi = js['single']['qushi']  ##趋势
    url = js['single']['url']  ##url
    zk_scname = js['single']['zk_scname']  ##来源
    changPriceRemark = js['single']['changPriceRemark']  ##趋势变动
    date_list = []  ##日期
    price_list = []  ##价格
    datalist = jiagequshi.replace('[Date.UTC(', '').replace(')', '').replace(']', '').split(',')
    # datalist2 = jiagequshiyh.replace('[Date.UTC(', '').replace(')', '').replace(']', '').replace('"', '').split(',')
    datalist2 = jiagequshiyh.replace('Date.UTC(', '')

    with UsingMysql(log_time=True) as um:
        insert_sql = "insert into product_info_base (product_name,picture,flag,net,lower_price,lower_date,qushi,qushiv) values (%s,%s,%s,%s,%s,%s,%s,%s)"
        um.cursor.execute(insert_sql, (title, pic, zk_scname, url, lowerPrice, lowerDate, qushi, changPriceRemark))
        um.cursor.close()

    datalist2 = datalist2.split("],[")
    for i in range(0, len(datalist2), 1):
        datalist2[i] = datalist2[i].replace("[", "").replace("]", "")

        promo = str(datalist2[i][datalist2[i].find('"'):]).replace("\"", "")

        dateprice = str(datalist2[i][0:datalist2[i].find(', "')]).replace(")", "").split(",")

        year = int(dateprice[0])
        mon = int(dateprice[1])
        day = int(dateprice[2])
        price = float(dateprice[3])


        # promo = datalist2[i + 4]

        date = datetime.date(year=year, month=mon, day=day)
        if year < 2021:
            continue

        with UsingMysql(log_time=True) as um:
            insert_sql = "insert into product_info (product_name,pro_date,price,picture,promo,flag,net) values (%s,%s,%s,%s,%s,%s,%s)"
            um.cursor.execute(insert_sql, (title, date, price, pic, promo, zk_scname, url))
            um.cursor.close()

        # date_list.append(date)
        # price_list.append(price)

    # data = {'date_日期': date_list, 'price_价格': price_list}
    # df = pd.DataFrame(data)
    # df.loc[:, "title_名称"] = title
    # df.loc[:, "pic_图片"] = pic
    # df.loc[:, "lowerPrice_最低价格"] = lowerPrice
    # df.loc[:, "lowerDate_最低价格日期"] = lowerDate
    # df.loc[:, "itemurl_商品链接"] = itemurl
    # df.loc[:, "qushi_趋势"] = qushi
    # df.loc[:, "changPriceRemark_趋势变动"] = changPriceRemark
    #
    # df.to_csv('out.csv', index=False, mode='a', encoding="GB18030")  ##保存数据
    # print(df)
    # return df


import pymysql
from timeit import default_timer

host = '150.158.80.216'
port = 3306
db = 'shop'
user = 'shop'
password = 'shop'


# ---- 用pymysql 操作数据库
def get_connection():
    conn = pymysql.connect(host=host, port=port, db=db, user=user, password=password)
    return conn


# ---- 使用 with 的方式来优化代码
class UsingMysql(object):
    def __init__(self, commit=True, log_time=True, log_label='总用时'):
        """
        :param commit: 是否在最后提交事务(设置为False的时候方便单元测试)
        :param log_time:  是否打印程序运行总时间
        :param log_label:  自定义log的文字
        """
        self._log_time = log_time
        self._commit = commit
        self._log_label = log_label

    def __enter__(self):

        # 如果需要记录时间
        if self._log_time is True:
            self._start = default_timer()

        # 在进入的时候自动获取连接和cursor
        conn = get_connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        # 提交事务
        if self._commit:
            self._conn.commit()
        # 在退出的时候自动关闭连接和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            # print('-- %s: %.6f 秒' % (self._log_label, diff))

    @property
    def cursor(self):
        return self._cursor


def check_it():
    count = 0
    with UsingMysql(log_time=True) as um:
        print("-- start -- ")
        for page in range(1, 21):
            insert_sql = "insert into test (value) values (%s)"
            um.cursor.execute(insert_sql, page)
            # print(count, title, area, unit_type, acreage, path, furnish, floor, buildtime, follow_man, update_time,house_type, tag, total_price, unit_price, sep=' |')


if __name__ == '__main__':
    # 538801983798
    # 533856110150
    # item = 'https://detail.tmall.com/item.htm?id=549474661845'  ##京东、淘宝、天猫等电商平台数据都可以获取
    # item = 'https://item.taobao.com/item.htm?id=536517932355'  ##京东、淘宝、天猫等电商平台数据都可以获取
    item = 'https://item.jd.com/10025849177679.html'  ##京东、淘宝、天猫等电商平台数据都可以获取
    # item = 'https://item.jd.com/100013116380.html'  ##京东、淘宝、天猫等电商平台数据都可以获取
    mmm(item)
    # a = "[Date.UTC(2020, 11, 24), 1799.00, \"购买1件,当前价格1799\"], [Date.UTC(2020, 11, 25), 1799.00, ""]"
    # a = a.replace('Date.UTC(', '')
    #
    # datalist2 = a.split("], [")
    #
    # for i in range(0, len(datalist2), 1):
    #     print(i)
    #     datalist2[i] = datalist2[i].replace("[", "").replace("]", "")
    #     print(datalist2[i])
    #     print(datalist2[i][datalist2[i].find('"'):])
    #     print(datalist2[i][0:datalist2[i].find(', "')])
